﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using NPOI;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using System.Globalization;
using System.IO;
using NPOI.HSSF.UserModel;

namespace ProjectStudy
{
    public partial class gridview导出为Excel : System.Web.UI.Page
    {
        
        protected void Page_Load(object sender, EventArgs e)
        {
            
            if (!IsPostBack)
            {                
                
            }
        }


        protected void btn_Export_Click(object sender, EventArgs e)
        {
            string path = "~/UploadFiles/";
            List<string> list =new string[]{"id","className","classDesc","isDel"}.ToList();

            ExportExcel(path, "Result", list);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="path">文件所在服务器路径</param>
        /// <param name="filename">文件名</param>
        /// <param name="colName">列名</param>
        protected void ExportExcel(string path,string filename,List<string> colName)
        {
            var en = new CultureInfo("en-US");
            var time = DateTime.Now.ToString("yyyyMMddHHmmssff", en);
            string path1 = Server.MapPath(path);
            path1 = path1 + filename + time + ".xlsx";
            DataTable dt = GetDataTable();

            TableToExcelForXLSX(dt, path1, "Result",colName);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dt">要导出的DataTable</param>
        /// <param name="path">文件路径</param>
        /// <param name="filename">文件名</param>
        /// <param name="colName">列名</param>
        private void TableToExcelForXLSX(DataTable dt, string path,string filename,List<string> colName)
        {
            XSSFWorkbook xssfworkbook = new XSSFWorkbook();
            ISheet sheet = xssfworkbook.CreateSheet("Result");
            var en = new CultureInfo("en-US");
            var time = DateTime.Now.ToString("yyyyMMddHHmmssff", en);

            //表头
            IRow row = sheet.CreateRow(0);
           
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    ICell cell = row.CreateCell(i);
                }
                for (int i = 0; i < colName.Count; i++)
                {
                    row.Cells[i].SetCellValue(colName[i]);
                }
                     

                //填充数据
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    IRow row1 = sheet.CreateRow(i + 1);

                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        ICell cell = row1.CreateCell(j);
                        
                        if (dt.Rows[i][colName[j]].ConvertObjToStr().Trim().Contains("<br/>"))
                        {
                            ICellStyle cs = xssfworkbook.CreateCellStyle();
                            cs.WrapText = true;               //设置单元格是否自动换行
                            sheet.SetColumnWidth(1, 20 * 256); //设置列宽
                            cell.CellStyle = cs;
                        }
                    }

                    for (int j = 0; j < colName.Count; j++)
                    {
                        string v = dt.Rows[i][colName[j]].ConvertObjToStr().Trim().Replace("<br/>", "\r\n");
                        row1.Cells[j].SetCellValue(v);
                        
                    }
                    
                }

                Response.Clear();
                Response.ContentType = "application/ms-excel";
                Response.AppendHeader("Content-Disposition", "attachment;filename=" + filename + time + ".xlsx");
 
            //转为字节数组   
            MemoryStream stream = new MemoryStream();
            xssfworkbook.Write(stream);
            byte[] buf = stream.ToArray();

            //保存为Excel文件   
            using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                Response.BinaryWrite(buf);
                fs.Flush();
            }

            FileInfo fileinfo = new FileInfo(path);
            if (fileinfo.Exists)
            {
                fileinfo.Delete();
            }
            Response.End();
        }

        protected void btn_search_Click(object sender, EventArgs e)
        {
            DataTable dt = GetDataTable();
            GridView1.DataSource = dt;
            GridView1.DataBind();
        }

        protected DataTable GetDataTable()
        { 
            string sql = "select * from Class1";
            DataTable dt = DBAccess.ExecuteDataTable(sql);
            return dt;
        }

        protected void btnImport_Click(object sender, EventArgs e)
        {
            if (FileUpload1.HasFile)
            {
                string path = Server.MapPath("~/UploadFiles/");
                var en = new CultureInfo("en-US");
                var time = DateTime.Now.ToString("yyyyMMddHHmmssff", en);
                path = path + time + ".xlsx";
                FileUpload1.SaveAs(path);

                DataTable dt= ExcelToTable(path,null);
                GridView1.DataSource = dt;
                GridView1.DataBind();

                File.Delete(path);
            }
            else
            { 
              //没有选择上传文件
            }
        }

        //------------------------Excel导入需要的方法(需引用NPOI)---------------------------------------
        private DataTable ExcelToTable(string path,List<int> listPKa)
        {
            DataTable dt = new DataTable();
            IWorkbook wb;
            using (FileStream fs = new FileStream(path, FileMode.Open))
            {
                wb = WorkbookFactory.Create(fs);
                if (HasData(wb))
                {
                    ISheet sheet = (ISheet)wb.GetSheetAt(0);

                    //表头  
                    #region
                    IRow header = sheet.GetRow(sheet.FirstRowNum);
                    List<int> columns = new List<int>();
                    for (int i = 0; i < header.LastCellNum; i++)
                    {
                        object obj = GetValueType(header.GetCell(i) as ICell);
                        if (obj == null || obj.ToString() == string.Empty)
                        {
                            dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                        }
                        else
                        {
                            dt.Columns.Add(new DataColumn(obj.ToString()));
                        }
                        columns.Add(i);
                    }
                    #endregion

                    //数据  
                    #region
                    for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                    {
                        DataRow dr = dt.NewRow();
                        bool hasPK = false;
                        IRow row = sheet.GetRow(i);
                        if (row == null)
                        {
                            continue;
                        }
                        foreach (int j in columns)
                        {
                            dr[j] = GetValueType(row.GetCell(j) as ICell);

                            //判断是否有PK列表
                            if(listPKa!=null)
                            {
                                if (listPKa.Count > 0)
                                {
                                    if (listPKa.Contains(j))
                                    {
                                        //排除所有主键都为空的行
                                        if (dr[j] != null && dr[j].ToString() != string.Empty)
                                        {
                                            dr[j] = dr[j].ToString();
                                            hasPK = true;
                                        }
                                    }
                                }
                            }
                        }
                        if (listPKa != null)
                        {
                            if (hasPK)
                            {
                                dt.Rows.Add(dr);
                            }
                        }
                        else
                        {
                            dt.Rows.Add(dr);
                        }
                    }
                    #endregion
                }
            }
            wb = null;
            return dt;
        }

        private object GetValueType(ICell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:  
                    return null;
                case CellType.Boolean: //BOOLEAN:  
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:  
                    if (DateUtil.IsCellDateFormatted(cell))
                    {
                        return cell.DateCellValue.ToString("yyyy/MM/dd");
                    }
                    else
                    {
                        return cell.NumericCellValue;
                    }
                case CellType.String: //STRING:  
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:  
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:
                    return cell.NumericCellValue;
                default:
                    return "=" + cell.CellFormula;
            }
        }

        private bool HasData(IWorkbook workbook)
        {
            if (workbook.NumberOfSheets > 0)
            {
                ISheet sheet = workbook.GetSheetAt(0);
                return sheet.PhysicalNumberOfRows > 0;
            }
            return false;
        }
        //------------------------Excel导入需要的方法(需引用NPOI)---------------------------------------
    }
}